package com.zretc.util;

import java.sql.*;
import javax.sql.rowset.CachedRowSet;
import com.sun.rowset.CachedRowSetImpl;

/**
 * JDBC-MYSQL数据库工具类
 * 
 * @author Administrator
 *
 */
public class DBUtil {
	// 静态常量
	private static final String DRIVER = "com.mysql.jdbc.Driver";
	private static final String URL = "jdbc:mysql://localhost:3306/electricity_management?useUnicode=true&characterEncoding=UTF-8&useOldAliasMetadataBehavior=true&useAffectedRows=true&serverTimezone=GMT";
	private static final String USER = "root";
	private static final String PASSWORD = "root";

	/**
	 * 连接数据库
	 * 
	 * @return 连接对象
	 */
	private static Connection getConnection() {
		Connection conn = null;
		try {
			Class.forName(DRIVER);
			conn = DriverManager.getConnection(URL, USER, PASSWORD);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 执行SELECT查询语句
	 * @param sql 执行SQL语句
	 * @param params SQL语句中所需参数
	 * @return 缓存结果集对象
	 */
	public static ResultSet doQuery(String sql,Object...params) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;// 结果集
		CachedRowSet cachedRowSet = null;	// 缓存结果集
		try {
			// 连接数据库
			conn = getConnection();
			// 创建预编译语句对象
			ps = conn.prepareStatement(sql);
			// 通过循环可变参数数组,对?赋值
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i+1, params[i]); 
			}
			// 执行SQL-->得到结果集
			rs = ps.executeQuery();
			
			cachedRowSet = new CachedRowSetImpl();
			cachedRowSet.populate(rs);	// void populate(ResultSet data) 将ResultSet对象中的数据存储到缓存结果集对象中
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 关闭资源
			closeAll(rs, ps, conn);
		}
		return cachedRowSet;  //返回缓存结果集对象,CachedRowSet是继承于ResultSet
	}
	
	/**
	 * 执行insert/update/delete语句
	 * @param sql 执行SQL语句
	 * @param params SQL语句中所需参数
	 * @return 影响行数
	 */
	public static int doUpdate(String sql,Object...params) {
		int n = 0;
		Connection conn = null;
		PreparedStatement ps = null;// 预编译语句对象
		try {
			conn = getConnection();

			ps = conn.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i+1, params[i]);
			}
			n = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 关闭
			closeAll(null, ps, conn);
		}
		return n;
	}
	
	/**
	 * 关闭数据库资源
	 * @param rs
	 * @param ps
	 * @param conn
	 */
	private  static void closeAll(ResultSet rs,PreparedStatement ps,Connection conn) {
		try {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
